﻿IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'INFO_CUSTOMER_HUNTING_RAW')
        DROP TABLE INFO_CUSTOMER_HUNTING_RAW

SELECT LEADID
,VPB_CIF
,COMPANY_NAME
,COMPANY_PHONE
,COMPANY_EMAIL
,STREET_AND_NUMBER AS COMPANY_ADDRESS
,[ID_NUMBER] AS LEGAL_ID
,PRIMARY_PHONE AS RELATION_PHONE
,[EMAIL] AS RELATION_EMAIL
,[ASSIGN_NAME] AS USER_DAO_NAME
,ASSIGN AS USER_DAO_ASSIGN
,CREATETIME 
,MODIFIEDTIME
,DESCRIPTION AS COMPANY_INFO
,FULL_NAME AS RELATION_NAME
,[PRODUCT_NO]
,[PRODUCT_NAME]
,[PRODUCT_CATEGORY]
,STATUS AS CONTACT_STATUS
,[LEAD_SOURCE]
,[LEAD_STATUS]
,MA_3_CHU AS BRANCH_CODE_MAP
,[TRUNG TAM] AS BRANCH_NAME_MAP
,[BRANH_CODE]
,[BRANCH_NAME]
,REGION
INTO INFO_CUSTOMER_HUNTING_RAW
FROM TUANLT4.CRM_TAM.DBO.LEAD_FINAL where  vpb_cif ='' and active = 0 and inactive = 0


select  * FROM TUANLT4.CRM_TAM.DBO.LEAD_FINAL order by assign  where assign like '%unlocated%'

--1.VN0010328      VN0010227
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010328'
where [BRANH_CODE]='VN0010227'
 
  --2.VN0010165   VN0010232
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010165'
where [BRANH_CODE]='VN0010232'
 
--VN0010193 VN0010330   Cầu Giấy    Region 1
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010193'
where [BRANH_CODE]='VN0010330'
 
--VN0010271 VN0010331   Vãn Quán    Region 1
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010271'
where [BRANH_CODE]='VN0010331'
 
 
--VN0010269 VN0010323   Gia Ðịnh    Region 5
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010269'
where [BRANH_CODE]='VN0010323'
 
--VN0010197 VN0010243   Cộng Hòa    Region 5
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010197'
where [BRANH_CODE]='VN0010243'
 
--VN0010182 VN0010334   Phú Lâm     Region 5
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010182'
where [BRANH_CODE]='VN0010334'
 
--VN0010185 VN0010333   Phú Mỹ Hưng Region 5
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010185'
where [BRANH_CODE]='VN0010333'
 
--VN0010124 VN0010332  tân phú
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010124'
where [BRANH_CODE]='VN0010332'
 
--VN0010124 VN0010332  tân phú
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010124'
where [BRANH_CODE]='VN0010332'
 
--thai ha
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010263'
where [BRANH_CODE]='VN0010252'
 
 
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010173'
where [BRANH_CODE]='VN0010114'
 
 
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010139'
where [BRANH_CODE]='VN0010340'
 
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010255'
where [BRANH_CODE]='VN0010122'
 --quan 3 - ben thanh
update INFO_CUSTOMER_HUNTING_RAW
set [BRANH_CODE] = 'VN0010351'
where [BRANH_CODE]='VN0010218'


IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'TBL_HUNTING_CUSTOMER')
        DROP TABLE TBL_HUNTING_CUSTOMER

SELECT A.LEADID
,A.VPB_CIF
,A.COMPANY_NAME
,A.COMPANY_PHONE
,A.COMPANY_EMAIL
,A.COMPANY_ADDRESS
,A.LEGAL_ID
,A.RELATION_PHONE
,A.RELATION_EMAIL
,A.USER_DAO_NAME
,A.USER_DAO_ASSIGN
,A.CREATETIME 
,A.MODIFIEDTIME
,A.COMPANY_INFO
,A.RELATION_NAME
,A.[PRODUCT_NO]
,A.[PRODUCT_NAME]
,A.[PRODUCT_CATEGORY]
,A.CONTACT_STATUS
,A.[LEAD_SOURCE]
,A.[LEAD_STATUS]
,A.BRANCH_NAME_MAP
,A.[BRANH_CODE]
,a.BRANCH_CODE_MAP
,A.[BRANCH_NAME]
,A.REGION AS REGION_MAP
,B.DAO
,B.DAO_NAME
,C.BRANCH_NAME_SME
,C.REGION
,C.BRANCH_CODE_SME
INTO TBL_HUNTING_CUSTOMER
FROM INFO_CUSTOMER_HUNTING_RAW A
LEFT JOIN TBL_HR B ON A.USER_DAO_ASSIGN = B.USER_NAME AND B.GROUP_ID = 4
LEFT JOIN TBL_BRANCH C ON (B.BRANCH_CODE_SME = C.BRANCH_CODE_SME)
WHERE LEAD_STATUS NOT IN ('Bad Lead','Not Interested','Not Qualified')

update a
set a.BRANCH_NAME_SME = b.BRANCH_NAME_SME, a.REGION = b.REGION, a.BRANCH_CODE_SME = b.BRANCH_CODE_SME
from TBL_HUNTING_CUSTOMER a, TBL_BRANCH b 
where a.BRANCH_NAME_SME is null and a.BRANCH_CODE_MAP collate database_default = b.BRANCH_CODE_SME

update a
set a.BRANCH_NAME_SME = b.BRANCH_NAME_SME, a.REGION = b.REGION, a.BRANCH_CODE_SME = b.BRANCH_CODE_SME
from TBL_HUNTING_CUSTOMER a, TBL_BRANCH b 
where a.BRANCH_NAME_SME is null and A.[BRANH_CODE] = b.BRANCH_ID


select * from TBL_HUNTING_CUSTOMER where BRANCH_NAME_SME is null
 select distinct contact_status from TBL_HUNTING_CUSTOMER

 delete from TBL_HUNTING_CUSTOMER where LEGAL_ID in (select legal_id from TBL_CUSTOMER)

select a.company_name, b.CUS_NAME, a.LEGAL_ID, b.LEGAL_ID, b.CIF,b.CUS_OPEN_DATE from TBL_HUNTING_CUSTOMER a, TBL_CUSTOMER b
where a.LEGAL_ID = b.LEGAL_ID

UPDATE TBL_HUNTING_CUSTOMER
SET REGION = REGION_MAP, DAO_NAME = USER_DAO_NAME, BRANCH_NAME_SME = BRANCH_NAME_MAP
WHERE BRANCH_NAME_SME IS NULL

SELECT DISTINCT(CONTACT_STATUS) FROM TBL_HUNTING_CUSTOMER
--select * from TBL_CUSTOMER
--SELECT * FROM TBL_HR WHERE USER_NAME = 'hungdd4'
--select * from TBL_CUSTOMER


delete from server12.SMECustomer360.[dbo].TBL_HUNTING_CUSTOMER
insert server12.SMECustomer360.dbo.TBL_HUNTING_CUSTOMER
select * from TBL_HUNTING_CUSTOMER